﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using Company.StudyClub.Model;

namespace Company.StudyClub.DAL.Course
{
    /// <summary>
    /// 数据访问类CatalogDAL。
    /// </summary>
    public class CatalogDAL
    {
        public CatalogDAL()
        { }
        /// <summary>
        ///  增加一条数据
        /// </summary>
        public int Add(Company.StudyClub.Model.Course_Catalog model)
        {
            int rowsAffected;
            SqlParameter[] parameters = {
					new SqlParameter("@CatalogID", SqlDbType.Int,4),
					new SqlParameter("@CatalogName", SqlDbType.NVarChar,100),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),                    
					new SqlParameter("@CreatorID", SqlDbType.Int,4),
                    new SqlParameter("@ParentID", SqlDbType.Int,4),
					new SqlParameter("@Sequence", SqlDbType.Int,4),
                    new SqlParameter("@TotalSubs", SqlDbType.Int,4),
					new SqlParameter("@TotalCourses", SqlDbType.Int,4),
                    new SqlParameter("@KeyString", SqlDbType.NVarChar,1000)};
            parameters[0].Direction = ParameterDirection.Output;
            if (model.CatalogName != null)
                parameters[1].Value = model.CatalogName;
            if (model.CreateTime != DateTime.Parse("1900-1-1"))
                parameters[2].Value = model.CreateTime;
            if (model.CreatorID != -1)
                parameters[3].Value = model.CreatorID;
            if (model.ParentID != -1)
                parameters[4].Value = model.ParentID;
            if (model.Sequence != -1)
                parameters[5].Value = model.Sequence;
            if (model.TotalSubs != -1)
                parameters[6].Value = model.TotalSubs;
            if (model.TotalCourses != -1)
                parameters[7].Value = model.TotalCourses;
            if (model.KeyString != null)
                parameters[8].Value = model.KeyString;

            LMSDbHelperSQL.RunProcedure("Course_Catalog_ADD", parameters, out rowsAffected);
            return (int)parameters[0].Value;
        }

        /// <summary>
        ///  更新一条数据
        /// </summary>
        public void Update(Company.StudyClub.Model.Course_Catalog model)
        {
            int rowsAffected;
            SqlParameter[] parameters = {
					new SqlParameter("@CatalogID", SqlDbType.Int,4),
					new SqlParameter("@CatalogName", SqlDbType.NVarChar,100),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@CreatorID", SqlDbType.Int,4),
                    new SqlParameter("@ParentID", SqlDbType.Int,4),
                    new SqlParameter("@KeyString", SqlDbType.NVarChar,1000)
                                        };
            if (model.CatalogID != -1)
                parameters[0].Value = model.CatalogID;
            if (model.CatalogName != null)
                parameters[1].Value = model.CatalogName;
            if (model.CreateTime != DateTime.Parse("1900-1-1"))
                parameters[2].Value = model.CreateTime;
            if (model.CreatorID != -1)
                parameters[3].Value = model.CreatorID;
            if (model.ParentID != -1)
                parameters[4].Value = model.ParentID;
            if (model.KeyString != null)
                parameters[5].Value = model.KeyString;

            LMSDbHelperSQL.RunProcedure("Course_Catalog_Update", parameters, out rowsAffected);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int CatalogID)
        {
            int rowsAffected;
            SqlParameter[] parameters = {
					new SqlParameter("@CatalogID", SqlDbType.Int,4)};
            parameters[0].Value = CatalogID;

            LMSDbHelperSQL.RunProcedure("Course_Catalog_Delete", parameters, out rowsAffected);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Company.StudyClub.Model.Course_Catalog GetModel(int CatalogID)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@CatalogID", SqlDbType.Int,4)};
            parameters[0].Value = CatalogID;

            Company.StudyClub.Model.Course_Catalog model = new Company.StudyClub.Model.Course_Catalog();
            using (SqlDataReader reader = LMSDbHelperSQL.RunProcedure("Course_Catalog_GetModel", parameters))
            {
                if (reader.Read())
                {
                    if (reader["CatalogID"].ToString() != "")
                    {
                        model.CatalogID = int.Parse(reader["CatalogID"].ToString());
                    }
                    model.CatalogName = reader["CatalogName"].ToString();
                    model.KeyString = reader["KeyString"].ToString();
                    if (reader["CreateTime"].ToString() != "")
                    {
                        model.CreateTime = DateTime.Parse(reader["CreateTime"].ToString());
                    }
                    if (reader["CreatorID"].ToString() != "")
                    {
                        model.CreatorID = int.Parse(reader["CreatorID"].ToString());
                    }
                    if (reader["ParentID"].ToString() != "")
                    {
                        model.ParentID = int.Parse(reader["ParentID"].ToString());
                    }
                    if (reader["Sequence"].ToString() != "")
                    {
                        model.Sequence = int.Parse(reader["Sequence"].ToString());
                    }
                    if (reader["TotalCourses"].ToString() != "")
                    {
                        model.TotalCourses = int.Parse(reader["TotalCourses"].ToString());
                    }
                    return model;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 根据上级目录ID得到目录列表
        /// </summary>
        /// <param name="parentId">上级目录ID</param>
        /// <returns>课程目录列表</returns>
        public List<Course_Catalog> GetCatalogsByParentID(int parentId)
        {
            List<Course_Catalog> objList = new List<Course_Catalog>();
            SqlParameter[] parameters = {
					new SqlParameter("@ParentID", SqlDbType.Int,4),
                  };
            parameters[0].Value = parentId;            

            using (SqlDataReader reader = LMSDbHelperSQL.RunProcedure("sp_Course_Catalog_GetCatalogList", parameters))
            {
                while (reader.Read())
                {
                    Course_Catalog model = new Course_Catalog();

                    model.CatalogID = (int)reader["CatalogID"];
                    model.CatalogName = reader["CatalogName"].ToString();
                    model.KeyString = reader["KeyString"].ToString();
                    model.CreatorID = (int)reader["CreatorID"];
                    model.Sequence = (int)reader["Sequence"];
                    model.ParentID = (int)reader["ParentID"];
                    model.CreateTime = (DateTime)reader["CreateTime"];
                    model.TotalSubs = (int)reader["TotalSubs"];
                    model.TotalCourses = (int)reader["TotalCourses"];
                    objList.Add(model);
                }

                return objList;
            }
        }

        /// <summary>
        /// 根据课程目录Keystring组合得到目录的完整名称
        /// </summary>
        /// <param name="catalogids">以逗号隔开的课程目录ID集合</param>
        /// <returns>课程目录完整名称</returns>
        public string GetFullCatalogName(string catalogids)
        {
            StringBuilder objSb = new StringBuilder();

            SqlParameter[] parameters = {
					new SqlParameter("@CatalogIDs", SqlDbType.NVarChar,1000),
                  };
            parameters[0].Value = catalogids;

            using (SqlDataReader reader = LMSDbHelperSQL.RunProcedure("sp_Course_Catalog_GetCatalogNameByIDs", parameters))
            {
                while (reader.Read())
                {         
                     objSb.Append(" > ");
                    objSb.Append(reader["CatalogName"]);                   
                }

                string catalogname = objSb.ToString();

                catalogname = catalogname.Substring(2);

                return catalogname;
            }
        }

        /// <summary>
        ///  更新扩展表数据
        /// </summary>
        public void UpdateExtend(Course_Catalog model)
        {
            int rowsAffected;
            SqlParameter[] parameters = {
                    new SqlParameter("@CatalogID", SqlDbType.Int,4),
					new SqlParameter("@Sequence", SqlDbType.Int,4),
                    new SqlParameter("@TotalSubs", SqlDbType.Int,4),
					new SqlParameter("@TotalCourses", SqlDbType.Int,4)
                                        };

            parameters[0].Value = model.CatalogID;
            parameters[1].Value = model.Sequence;
            parameters[2].Value = model.TotalSubs;
            parameters[3].Value = model.TotalCourses;

            LMSDbHelperSQL.RunProcedure("Course_CatalogExtend_Update", parameters, out rowsAffected);
        }

        /// <summary>
        ///  更新子目录的KeyString
        /// </summary>
        /// <param name="keystring">要更新的内容</param>
        /// <param name="newkeystring">新的内容</param>
        /// <returns>成功：true,失败：false</returns>
        public bool UpdateKeyString(string keystring, string newkeystring)
        {
            int rowsAffected;
            SqlParameter[] parameters = {                    
					new SqlParameter("@NewKeyString", SqlDbType.NVarChar,1000),
                    new SqlParameter("@KeyString", SqlDbType.NVarChar,1000)
                                        };

            parameters[0].Value = newkeystring;
            parameters[1].Value = keystring;

            LMSDbHelperSQL.RunProcedure("sp_Course_Catalog_UpdateKeyString", parameters, out rowsAffected);

            return rowsAffected > 0;
        }
    }
}

